﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using CqusInfo.CR.SqlHelper;
using System.Data;
using System.Data.SQLite;
using CqusInfo.RAL.User;

namespace CqusInfo.DAL.User
{
    public class User_BlackDAL:Interface.IUser_BlackDAL
    {
        public Int32 GetCounts(bool isDel)
        {
            return SQLiteHelper.GetCounts("tb_User_Black", isDel);
        }

        /// <summary>
        /// 依据用户名或者管理员查找信息
        /// </summary>
        /// <param name="admin">管理员</param>
        /// <param name="userName">用户名</param>
        /// <param name="page">第几页，需减一</param>
        /// <param name="takeItem">每页显示的信息</param>
        /// <returns>DataSet</returns>
        private DataSet Query_DB(string admin, string userName, Int32 page, Int32 takeItem)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("select * from tb_User_Black limit " + (page - 1) * takeItem + "," + takeItem);

            string feildName = admin.Trim() == null ? "c_vc_userName" : "c_vc_admin";
            string feildValue = admin.Trim() == null ? "userName" : "admin";
            sqlStr.Append(" where " + feildName + "=@" + feildValue);

            SQLiteParameter[] parameters ={
                                        new SQLiteParameter("@userName",DbType.String),
                                        new SQLiteParameter("@admin",DbType.String)
                                        };
            parameters[0].Value = userName;
            parameters[1].Value = admin;

            return SQLiteHelper.ExecuteQuery(sqlStr.ToString(), parameters);
        }
        public List<RAL.User.User_BlackModel> Query(string admin, string userName, Int32 page, Int32 takeItem)
        {
            List<User_BlackModel> ubList = new List<User_BlackModel>();
            DataSet ds = Query_DB(admin, userName, page, takeItem);
            string value = admin.Trim() == null ? userName : admin;
            string cacheId = string.Format("User_BlackModel_{0}", value);
            ubList = SqlData.GetQueryList<User_BlackModel>(cacheId, "User_BlackModel", ds);
            return ubList;
        }

        public List<RAL.User.User_BlackModel> Query(Int32 page, Int32 takeItem, bool isBlack, bool isDel)
        {
            List<User_BlackModel> ubList = new List<User_BlackModel>();
            DataSet ds = SQLiteHelper.Query("tb_User_Black", "c_b_black", isBlack, "c_b_isDel", isDel, page, takeItem);
            string cacheId = string.Format("User_BlackModel_{0}_{1}", isBlack, isDel);
            ubList = SqlData.GetQueryList<User_BlackModel>(cacheId, "User_BlackModel", ds);
            return ubList;
        }

        /// <summary>
        /// 查找用户的警告次数
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>警告次数</returns>
        public Int32 Query_DB(string userName)
        {
            DataSet ds = SQLiteHelper.Query("tb_User_Black", "c_vc_userName", userName);
            Int32 myCounts = 0;
            myCounts = Convert.ToInt32(ds.Tables[0].Rows[0]["c_i_warningNO"]);
            return myCounts;
        }
        public Int32 Query(string userName)
        {
            return Query_DB(userName);
        }

        public Int32 Add(RAL.User.User_BlackModel model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert into tb_User_Black values(");
            sqlStr.Append("c_vc_ubId=@ubId,c_vc_admin=@admin,c_vc_userName=@userName,c_i_type=@type,c_i_warningNO=0,c_b_black=false,c_dt_dateTime=@dateTime,c_b_isDel=false)");

            SQLiteParameter[] parameters ={
                                         new SQLiteParameter("@ubId",DbType.String),
                                         new SQLiteParameter("@admin",DbType.String),
                                         new SQLiteParameter("@userName",DbType.String),
                                         new SQLiteParameter("@type",DbType.Int32,4),
                                         new SQLiteParameter("@dateTime",DbType.DateTime)
                                          };
            parameters[0].Value = model.c_vc_ubId;
            parameters[1].Value = model.c_vc_admin;
            parameters[2].Value = model.c_vc_userName;
            parameters[3].Value = model.c_i_type;
            parameters[4].Value = model.c_dt_dateTime;

            return SQLiteHelper.ExecuteNonQuery(sqlStr.ToString(), parameters);
        }

        public Int32 Delete(string ubId, bool isDel, bool trueDel)
        {
            if (trueDel)
                return SQLiteHelper.Delete("tb_User_Black", "c_vc_userName", ubId);
            else
                return SQLiteHelper.Delete("tb_User_Black", "c_vc_userName", ubId, "c_b_isDel", isDel);
        }

        public bool Update(string userName)
        {
            StringBuilder sqlStr = new StringBuilder();
            Int32 warningNO=Query_DB(userName);
            sqlStr.Append("update tb_User_Black set c_i_warningNO=@warning where c_vc_userName=@userName and c_b_black=false");

            SQLiteParameter[] parameters ={
                                         new SQLiteParameter("@userName",DbType.String),
                                         new SQLiteParameter("@warning",DbType.Int32,4)
                                         };
            parameters[0].Value = userName;
            parameters[0].Value = warningNO;

            return Convert.ToBoolean(SQLiteHelper.ExecuteNonQuery(sqlStr.ToString(), parameters));
        }

        public bool Update(string userName, bool isBlack)
        {
            return Convert.ToBoolean(SQLiteHelper.Delete("tb_User_Black", "c_vc_userName", userName, "c_b_Black", isBlack));
        }
    }
}
